<?php
class ACardWapmaster extends CActiveRecord
{
    public function getAllProviderCode()
    {
        $sql = "SELECT DISTINCT (provider_code) FROM c_card_log WHERE provider_code!='' ORDER BY provider_code";
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    public function GetUser($search, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["provider_code"]) ? " AND provider_code='" . mysql_escape_string($search["provider_code"]) . "'" : "";        
        $condition .= !empty($search["price"]) ? " AND price='" . mysql_escape_string($search["price"]) . "'" : "";                
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT userId, sum(price) as total_price FROM c_card_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        
        if($search["order"]=="1") {
            /* Sắp xếp theo doanh thu từ thấp đến cao */
            $sql = "SELECT userId, sum(price) as total_price FROM c_card_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="2") {
            /* Sắp xếp theo doanh thu từ cao đến thấp */
            $sql = "SELECT userId, sum(price) as total_price FROM c_card_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="3") {
            /* Sắp xếp theo sản lượng từ thấp đến cao */
            $sql = "SELECT userId, count(id) as total_card FROM c_card_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_card ASC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        } else if($search["order"]=="4") {
            /* Sắp xếp theo sản lượng từ cao đến thấp */
            $sql = "SELECT userId, count(id) as total_card FROM c_card_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_card DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        }  
        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();    
        return $rows;
    }
    
    public function GetTotalUser($search)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";        
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["provider_code"]) ? " AND provider_code='" . mysql_escape_string($search["provider_code"]) . "'" : "";        
        $condition .= !empty($search["price"]) ? " AND price='" . mysql_escape_string($search["price"]) . "'" : "";                
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
                
        $sql = "SELECT count(DISTINCT userId) FROM c_card_log WHERE 1 " . $condition;                        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();    
    
        return $rows["count(DISTINCT userId)"];    
    }
    
    public function getReport($search, $rate_card)
    {
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= $search["list_user_id"]!="" ? " AND userId IN (" . mysql_escape_string($search["list_user_id"]) . ")" : "";
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["provider_code"]) ? " AND provider_code='" . mysql_escape_string($search["provider_code"]) . "'" : "";        
        $condition .= !empty($search["price"]) ? " AND price='" . mysql_escape_string($search["price"]) . "'" : "";                
        $condition .= " AND status=1";
                
        $sql = "SELECT count(id), userId, sum(price), price, month, year, dataId, percent_wapmaster, percent_partner FROM c_card_log WHERE type=1 " . $condition . " GROUP BY userId, dataId, price, month, year, percent_wapmaster, percent_partner";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
         
        foreach($rows as $row){
            $id = $row["userId"];                        
            $percent_wapmaster = $row["percent_wapmaster"];
            $percent_partner = $row["percent_partner"];
            
            if(!isset($card[$id]["total_card"])) $card[$id]["total_card"] = 0;
            
            if(!isset($card[$id]["10000"])) $card[$id]["10000"] = 0;
            if(!isset($card[$id]["20000"])) $card[$id]["20000"] = 0;
            if(!isset($card[$id]["30000"])) $card[$id]["30000"] = 0;
            if(!isset($card[$id]["50000"])) $card[$id]["50000"] = 0;
            
            if(!isset($card[$id]["100000"])) $card[$id]["100000"] = 0;
            if(!isset($card[$id]["200000"])) $card[$id]["200000"] = 0;
            if(!isset($card[$id]["300000"])) $card[$id]["300000"] = 0;
            if(!isset($card[$id]["500000"])) $card[$id]["500000"] = 0;
            
            if(!isset($card[$id]["telco"])) $card[$id]["telco"] = 0;            
            if(!isset($card[$id]["wapmaster"])) $card[$id]["wapmaster"] = 0;
            if(!isset($card[$id]["mtop"])) $card[$id]["mtop"] = 0;
            if(!isset($card[$id]["partner"])) $card[$id]["partner"] = 0;
            
            $card[$id]["total_card"] += $row["count(id)"];            
            $card[$id]["10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $card[$id]["20000"] += $row["price"]==20000 ? $row["count(id)"] : 0;
            $card[$id]["30000"] += $row["price"]==30000 ? $row["count(id)"] : 0;
            $card[$id]["50000"] += $row["price"]==50000 ? $row["count(id)"] : 0;
            
            $card[$id]["100000"] += $row["price"]==100000 ? $row["count(id)"] : 0;
            $card[$id]["200000"] += $row["price"]==200000 ? $row["count(id)"] : 0;
            $card[$id]["300000"] += $row["price"]==300000 ? $row["count(id)"] : 0;
            $card[$id]["500000"] += $row["price"]==500000 ? $row["count(id)"] : 0;
            
            $total_money = $row["sum(price)"];
            
            $index_rate = $row["month"] . "_" . $row["year"];
            $rateCard = $rate_card[$index_rate] / 100;
            
            $card[$id]["telco"] += $total_money * $rateCard;              
            $card[$id]["wapmaster"] += $total_money * $rateCard * ($percent_wapmaster / 100);
            $card[$id]["partner"] += $total_money * $rateCard * ($percent_partner / 100);
            $card[$id]["mtop"] += $total_money * $rateCard * ((100 - $percent_partner - $percent_wapmaster) / 100);
        }
             
        $sql = "SELECT id, username FROM c_user WHERE id IN (" . $search["list_user_id"] . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsUser = $command->queryAll();
        $replaceUser = array();
        foreach($rowsUser as $row){
            $replaceUser[$row["id"]]["id"] = $row["id"];
            $replaceUser[$row["id"]]["username"] = $row["username"];            
        }
                
        foreach($card as $i=>$row){
            $card[$i]["username"] = isset($replaceUser[$i]["username"]) ? $replaceUser[$i]["username"] : "";            
        }
                  
        $users = explode(", ", $search["list_user_id"]);
        $response = array();
        foreach($users as $id){
            if(isset($card[$id])){
                $response[$id] = $card[$id];
            }   
        }
        
        return $response;
    }
    
    public function getReportByUser($search, $rate_card, $currentPage, $numberRecordPerPage)
    {        
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= " AND userId=" . mysql_escape_string($search["userId"]);
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["provider_code"]) ? " AND provider_code='" . mysql_escape_string($search["provider_code"]) . "'" : "";        
        $condition .= !empty($search["price"]) ? " AND price='" . mysql_escape_string($search["price"]) . "'" : "";                
        $condition .= " AND status=1";                
        
        if(!empty($search["keyword"])){
            if($search["type"]==1){
                $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
            }
            
            if($search["type"]==2){
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=1";
            }     
        }
        
        $sql = "SELECT * FROM c_card_log WHERE 1 " . $condition . " ORDER BY create_date DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $list_partner_id = "0";
        $list_game_id = "0";        
        foreach($rows as $row){
            $list_partner_id .= "," . $row["partnerId"];
            $list_game_id .= $row["type"]=="1" ? "," . $row["dataId"] : "";            
        }
        
        $list_partner_id = ltrim($list_partner_id, ",");
        $list_game_id = ltrim($list_game_id, ",");
        
        $list_partner_id = Common::remove_duplicate($list_partner_id);
        $list_game_id = Common::remove_duplicate($list_game_id);
        
        $sql = "SELECT id, name FROM c_partner WHERE id IN (" . $list_partner_id . ")";
        $command = Yii::app()->db->createCommand($sql);
        $rowsPartner = $command->queryAll();
        $replacePartner = array();
        
        foreach($rowsPartner as $row){
            $replacePartner[$row["id"]] = $row["name"];
        }
        
        if(!empty($list_game_id)){
            $sql = "SELECT id, title, isOnline, isSms FROM c_game WHERE id IN (" . $list_game_id . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsGame = $command->queryAll();
            $replaceGame = array();
            
            foreach($rowsGame as $row){
                $replaceGame[$row["id"]]["title"] = $row["title"];
                $replaceGame[$row["id"]]["isOnline"] = $row["isOnline"];
                $replaceGame[$row["id"]]["isSms"] = $row["isSms"];
            }
        }
                   
        $i = 0;
        while(isset($rows[$i])){
            $type = $rows[$i]["type"];
            $dataId = $rows[$i]["dataId"];            
            $partnerId = $rows[$i]["partnerId"];            
            
            $rows[$i]["partner_name"] = isset($replacePartner[$partnerId]) ? $replacePartner[$partnerId] : "Không xác định";
            
            if($type=="1"){
                
                $rows[$i]["title"] = isset($replaceGame[$dataId]["title"]) ? $replaceGame[$dataId]["title"] : "Không xác định";
                if(isset($replaceGame[$dataId])){
                    $isOnline = $replaceGame[$dataId]["isOnline"];
                    $isSms = $replaceGame[$dataId]["isSms"];
                    if($isOnline==1){
                        $rows[$i]["content_type"] = "Game Online";
                    } else if($isSms==1){
                        $rows[$i]["content_type"] = "Game Offline Sms";
                    } else {
                        $rows[$i]["content_type"] = "Game Offline";
                    }    
                } else {
                    $rows[$i]["content_type"] = "Không xác định";    
                }
                
            }
            
            $index_rate = $rows[$i]["month"] . "_" . $rows[$i]["year"];
            $rateCard = $rate_card[$index_rate] / 100;
            
            $rows[$i]["telco"] = $rows[$i]["price"] * $rateCard;              
            if($rows[$i]["type"]=="1"){
                $rows[$i]["wapmaster"] = $rows[$i]["telco"] * ($rows[$i]["percent_wapmaster"] / 100);
                $rows[$i]["partner"] = $rows[$i]["telco"] * ($rows[$i]["percent_partner"] / 100);
                $rows[$i]["mtop"] = $rows[$i]["telco"] * ((100 - $rows[$i]["percent_wapmaster"] - $rows[$i]["percent_partner"]) / 100);
            } 
            
            $i++;
        }
        
        return $rows;
    }
    
    public function getTotalReportByUser($search, $rate_card)
    {        
        $search["fromDate"] .= " 23:59:59";
        $condition = "";
        $condition .= " AND userId=" . mysql_escape_string($search["userId"]);
        $condition .= " AND create_date>='" . strtotime($search["toDate"]) . "'";
        $condition .= " AND create_date<='" . strtotime($search["fromDate"]) . "'";        
        $condition .= !empty($search["provider_code"]) ? " AND provider_code='" . mysql_escape_string($search["provider_code"]) . "'" : "";        
        $condition .= !empty($search["price"]) ? " AND price='" . mysql_escape_string($search["price"]) . "'" : "";                
        $condition .= " AND status=1";                
        
        if(!empty($search["keyword"])){
            if($search["type"]==1){
                $sql = "SELECT id FROM c_partner WHERE name='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND partnerId=0" : " AND partnerId=" . $rows["id"];
            }
            
            if($search["type"]==2){
                $sql = "SELECT id FROM c_game WHERE title='" . mysql_escape_string($search["keyword"]) . "'";
                $command = Yii::app()->db->createCommand($sql);
                $rows = $command->queryRow();    
                
                $condition .= empty($rows) ? " AND 0" : " AND dataId=" . $rows["id"] . " AND type=1";
            }     
        }
        
        $sql = "SELECT count(id) FROM c_card_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();        
        return $rows["count(id)"];
    }
}  
?>
